{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10098 entries, 0 to 10097\n",
      "Data columns (total 5 columns):\n",
      "did              10098 non-null int64\n",
      "communityName    10098 non-null int64\n",
      "id               10098 non-null object\n",
      "lat              10098 non-null float64\n",
      "lng              10098 non-null float64\n",
      "dtypes: float64(2), int64(2), object(1)\n",
      "memory usage: 394.5+ KB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_latlng = pd.read_csv(\"latlng.csv\",skiprows=[0],names=[\"did\",\"communityName\",\"id\",\"lat\",\"lng\"])\n",
    "df_latlng.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 10106 entries, 0 to 10105\n",
      "Data columns (total 14 columns):\n",
      "totalprice     10106 non-null float64\n",
      "unitprice      10106 non-null int64\n",
      "position       10106 non-null object\n",
      "rooms          10106 non-null object\n",
      "size           10106 non-null float64\n",
      "orientation    10106 non-null object\n",
      "decoration     10106 non-null object\n",
      "elevator       10106 non-null object\n",
      "area           10106 non-null object\n",
      "height         10106 non-null object\n",
      "year           10106 non-null int64\n",
      "buildtype      10106 non-null object\n",
      "follow         10106 non-null int64\n",
      "city           10106 non-null object\n",
      "dtypes: float64(2), int64(3), object(9)\n",
      "memory usage: 1.2+ MB\n"
     ]
    }
   ],
   "source": [
    "house = pd.read_excel(\"house.xls\")\n",
    "house.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>communityName</th>\n",
       "      <th>id</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10093</th>\n",
       "      <td>10101</td>\n",
       "      <td>海川阁</td>\n",
       "      <td>22.592468</td>\n",
       "      <td>114.278616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10094</th>\n",
       "      <td>10102</td>\n",
       "      <td>盛世名门</td>\n",
       "      <td>22.555490</td>\n",
       "      <td>114.235915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10095</th>\n",
       "      <td>10103</td>\n",
       "      <td>海川阁</td>\n",
       "      <td>22.592468</td>\n",
       "      <td>114.278616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10096</th>\n",
       "      <td>10104</td>\n",
       "      <td>云顶天海</td>\n",
       "      <td>22.606376</td>\n",
       "      <td>114.312057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10097</th>\n",
       "      <td>10105</td>\n",
       "      <td>中海半山溪谷花园</td>\n",
       "      <td>22.592729</td>\n",
       "      <td>114.249050</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       communityName         id        lat         lng\n",
       "10093          10101       海川阁   22.592468  114.278616\n",
       "10094          10102      盛世名门   22.555490  114.235915\n",
       "10095          10103       海川阁   22.592468  114.278616\n",
       "10096          10104      云顶天海   22.606376  114.312057\n",
       "10097          10105  中海半山溪谷花园   22.592729  114.249050"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del df_latlng['did']\n",
    "df_latlng.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>communityName</th>\n",
       "      <th>id</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>totalprice</th>\n",
       "      <th>unitprice</th>\n",
       "      <th>position</th>\n",
       "      <th>rooms</th>\n",
       "      <th>size</th>\n",
       "      <th>orientation</th>\n",
       "      <th>decoration</th>\n",
       "      <th>elevator</th>\n",
       "      <th>area</th>\n",
       "      <th>height</th>\n",
       "      <th>year</th>\n",
       "      <th>buildtype</th>\n",
       "      <th>follow</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10093</th>\n",
       "      <td>10101</td>\n",
       "      <td>海川阁</td>\n",
       "      <td>22.592468</td>\n",
       "      <td>114.278616</td>\n",
       "      <td>270.0</td>\n",
       "      <td>38939</td>\n",
       "      <td>海川阁</td>\n",
       "      <td>2室1厅</td>\n",
       "      <td>69.34</td>\n",
       "      <td>东南</td>\n",
       "      <td>其他</td>\n",
       "      <td>有电梯</td>\n",
       "      <td>盐田港</td>\n",
       "      <td>高</td>\n",
       "      <td>2011</td>\n",
       "      <td>塔楼</td>\n",
       "      <td>1</td>\n",
       "      <td>盐田</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10094</th>\n",
       "      <td>10102</td>\n",
       "      <td>盛世名门</td>\n",
       "      <td>22.555490</td>\n",
       "      <td>114.235915</td>\n",
       "      <td>529.0</td>\n",
       "      <td>59991</td>\n",
       "      <td>盛世名门</td>\n",
       "      <td>3室1厅</td>\n",
       "      <td>88.18</td>\n",
       "      <td>东南</td>\n",
       "      <td>其他</td>\n",
       "      <td>有电梯</td>\n",
       "      <td>沙头角</td>\n",
       "      <td>低</td>\n",
       "      <td>2009</td>\n",
       "      <td>板塔结合</td>\n",
       "      <td>1</td>\n",
       "      <td>盐田</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10095</th>\n",
       "      <td>10103</td>\n",
       "      <td>海川阁</td>\n",
       "      <td>22.592468</td>\n",
       "      <td>114.278616</td>\n",
       "      <td>180.0</td>\n",
       "      <td>38209</td>\n",
       "      <td>海川阁</td>\n",
       "      <td>1室1厅</td>\n",
       "      <td>47.11</td>\n",
       "      <td>东南</td>\n",
       "      <td>其他</td>\n",
       "      <td>有电梯</td>\n",
       "      <td>盐田港</td>\n",
       "      <td>高</td>\n",
       "      <td>2011</td>\n",
       "      <td>塔楼</td>\n",
       "      <td>1</td>\n",
       "      <td>盐田</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10096</th>\n",
       "      <td>10104</td>\n",
       "      <td>云顶天海</td>\n",
       "      <td>22.606376</td>\n",
       "      <td>114.312057</td>\n",
       "      <td>150.0</td>\n",
       "      <td>39216</td>\n",
       "      <td>云顶天海</td>\n",
       "      <td>1室0厅</td>\n",
       "      <td>38.25</td>\n",
       "      <td>东南</td>\n",
       "      <td>其他</td>\n",
       "      <td>有电梯</td>\n",
       "      <td>梅沙</td>\n",
       "      <td>低</td>\n",
       "      <td>2006</td>\n",
       "      <td>板楼</td>\n",
       "      <td>0</td>\n",
       "      <td>盐田</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10097</th>\n",
       "      <td>10105</td>\n",
       "      <td>中海半山溪谷花园</td>\n",
       "      <td>22.592729</td>\n",
       "      <td>114.249050</td>\n",
       "      <td>300.0</td>\n",
       "      <td>41012</td>\n",
       "      <td>中海半山溪谷花园</td>\n",
       "      <td>2室1厅</td>\n",
       "      <td>73.15</td>\n",
       "      <td>南 西南</td>\n",
       "      <td>其他</td>\n",
       "      <td>有电梯</td>\n",
       "      <td>盐田港</td>\n",
       "      <td>中</td>\n",
       "      <td>2008</td>\n",
       "      <td>板塔结合</td>\n",
       "      <td>0</td>\n",
       "      <td>盐田</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       communityName         id        lat         lng  totalprice  unitprice  \\\n",
       "10093          10101       海川阁   22.592468  114.278616       270.0      38939   \n",
       "10094          10102      盛世名门   22.555490  114.235915       529.0      59991   \n",
       "10095          10103       海川阁   22.592468  114.278616       180.0      38209   \n",
       "10096          10104      云顶天海   22.606376  114.312057       150.0      39216   \n",
       "10097          10105  中海半山溪谷花园   22.592729  114.249050       300.0      41012   \n",
       "\n",
       "        position rooms   size orientation decoration elevator area height  \\\n",
       "10093       海川阁   2室1厅  69.34          东南         其他      有电梯  盐田港      高   \n",
       "10094      盛世名门   3室1厅  88.18          东南         其他      有电梯  沙头角      低   \n",
       "10095       海川阁   1室1厅  47.11          东南         其他      有电梯  盐田港      高   \n",
       "10096      云顶天海   1室0厅  38.25          东南         其他      有电梯   梅沙      低   \n",
       "10097  中海半山溪谷花园   2室1厅  73.15        南 西南         其他      有电梯  盐田港      中   \n",
       "\n",
       "       year buildtype  follow city  \n",
       "10093  2011        塔楼       1   盐田  \n",
       "10094  2009      板塔结合       1   盐田  \n",
       "10095  2011        塔楼       1   盐田  \n",
       "10096  2006        板楼       0   盐田  \n",
       "10097  2008      板塔结合       0   盐田  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge=pd.merge(df_latlng, house, left_on='communityName', right_index=True)\n",
    "df_merge.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"\"\"4、生成需要的格式文件\"\"\"\n",
    "# out_map = \"total.txt\"\n",
    "# with open(out_map,\"w\") as file_out:\n",
    "#     for lng,lat,price in zip(list(df_merge[\"lng\"]),list(df_merge[\"lat\"]),list(df_merge[\"totalprice\"])):\n",
    "# #         out = str(lng)+\",\"+str(lat)\n",
    "#         out='{\\\"lng\\\":'+str(lng)+',\\\"lat\\\":'+str(lat)+',\\\"count\\\":'+str(price)+'},'\n",
    "#         file_out.write(out)\n",
    "#         file_out.write(\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 10098 entries, 0 to 10097\n",
      "Data columns (total 18 columns):\n",
      "communityName    10098 non-null int64\n",
      "id               10098 non-null object\n",
      "lat              10098 non-null float64\n",
      "lng              10098 non-null float64\n",
      "totalprice       10098 non-null float64\n",
      "unitprice        10098 non-null int64\n",
      "position         10098 non-null object\n",
      "rooms            10098 non-null object\n",
      "size             10098 non-null float64\n",
      "orientation      10098 non-null object\n",
      "decoration       10098 non-null object\n",
      "elevator         10098 non-null object\n",
      "area             10098 non-null object\n",
      "height           10098 non-null object\n",
      "year             10098 non-null int64\n",
      "buildtype        10098 non-null object\n",
      "follow           10098 non-null int64\n",
      "city             10098 non-null object\n",
      "dtypes: float64(4), int64(4), object(10)\n",
      "memory usage: 1.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df_merge.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
